<!DOCTYPE html>
<!--[if lt IE 7]> <html class="no-js lt-ie9 lt-ie8 lt-ie7"> <![endif]-->
<!--[if IE 7]> <html class="no-js lt-ie9 lt-ie8"> <![endif]-->
<!--[if IE 8]> <html class="no-js lt-ie9"> <![endif]-->
<!--[if gt IE 8]><!--> <html class="no-js"> <!--<![endif]-->
<head>
  <meta http-equiv="content-type" content="text/html; charset=utf-8">
  <title>SQL优化  &middot; sitename</title>
  <meta http-equiv="X-UA-Compatible" content="IE=edge">
<meta name="HandheldFriendly" content="True">
<meta name="MobileOptimized" content="320">
<meta name="viewport" content="width=device-width, initial-scale=1">


<meta name="description" content="SQL优化 description" />

<meta name="keywords" content="one, two, ">


<meta property="og:title" content="SQL优化  &middot; sitename ">
<meta property="og:site_name" content="sitename"/>
<meta property="og:url" content="https://lvzongcheng.gitee.io/sql/sql%E4%BC%98%E5%8C%96/" />
<meta property="og:locale" content="en-EN">


<meta property="og:type" content="article" />
<meta property="og:description" content="SQL优化 description"/>
<meta property="og:article:published_time" content="2017-05-13T00:00:00Z" />
<meta property="og:article:modified_time" content="2017-05-13T00:00:00Z" />

  
    
<meta property="og:article:tag" content="one">
    
<meta property="og:article:tag" content="two">
    
  

  

<script type="application/ld+json">
  {
    "@context": "http://schema.org",
    "@type": "Article",
    "headline": "SQL优化",
    "author": {
      "@type": "Person",
      "name": "LvZongCheng"
    },
    "datePublished": "2017-05-13",
    "description": "SQL优化 description",
    "wordCount":  343 
  }
</script>



<link rel="canonical" href="https://lvzongcheng.gitee.io/sql/sql%E4%BC%98%E5%8C%96/" />

<link rel="apple-touch-icon-precomposed" sizes="144x144" href="https://lvzongcheng.gitee.io/touch-icon-144-precomposed.png">
<link href="https://lvzongcheng.gitee.io/favicon.png" rel="icon">

<meta name="generator" content="Hugo 0.60.0" />

  <!--[if lt IE 9]>
<script src="https://oss.maxcdn.com/libs/html5shiv/3.7.0/html5shiv.js"></script>
<script src="https://oss.maxcdn.com/libs/respond.js/1.4.2/respond.min.js"></script>
<![endif]-->

<link href='https://fonts.googleapis.com/css?family=Merriweather:300%7CRaleway%7COpen+Sans' rel='stylesheet' type='text/css'>
<link rel="stylesheet" href="/css/font-awesome.min.css">
<link rel="stylesheet" href="/css/style.css">
<link rel="stylesheet" href="/css/highlight/default.css">

  
  
	<script>
	  (function(i,s,o,g,r,a,m){i['GoogleAnalyticsObject']=r;i[r]=i[r]||function(){
	  (i[r].q=i[r].q||[]).push(arguments)},i[r].l=1*new Date();a=s.createElement(o),
	  m=s.getElementsByTagName(o)[0];a.async=1;a.src=g;m.parentNode.insertBefore(a,m)
	  })(window,document,'script','//www.google-analytics.com/analytics.js','ga');

	  ga('create', 'Your Google Analytics tracking code', 'auto');
	  ga('send', 'pageview');

	</script>

</head>
<body>
  <main id="main-wrapper" class="container main_wrapper has-sidebar">
    <header id="main-header" class="container main_header">
  <div class="container brand">
  <div class="container title h1-like">
  <a class="baselink" href="https://lvzongcheng.gitee.io">
  LvZongCheng

</a>

</div>

  
<div class="container topline">
  
  淡定 执著 进取 自律


</div>


</div>

  <nav class="container nav primary no-print">
  

<a class="homelink" href="https://lvzongcheng.gitee.io">home</a>


  
<a href="https://lvzongcheng.gitee.io/framework" title="Show list of Frameworks">Frameworks</a>

<a href="https://lvzongcheng.gitee.io/java" title="Show list of Java">Java</a>

<a href="https://lvzongcheng.gitee.io/linux" title="Show list of Linux">Linux</a>

<a href="https://lvzongcheng.gitee.io/redis" title="Show list of Redis">Redis</a>

<a href="https://lvzongcheng.gitee.io/sql" title="Show list of SQL">SQL</a>

<a href="https://lvzongcheng.gitee.io/Spring" title="Show list of Spring">Spring</a>

<a href="https://lvzongcheng.gitee.io/tools" title="Show list of Tools">Tools</a>

<a href="https://lvzongcheng.gitee.io/webpage" title="Show list of WebPage">WebPage</a>

<a href="https://lvzongcheng.gitee.io/information" title="Show list of information">information</a>

<a href="https://lvzongcheng.gitee.io/network" title="Show list of network">network</a>

<a href="https://lvzongcheng.gitee.io/%E5%BE%AE%E6%9C%8D%E5%8A%A1" title="Show list of 微服务">微服务</a>

<a href="https://lvzongcheng.gitee.io/%E6%80%BB%E7%BB%93" title="Show list of 总结">总结</a>

<a href="https://lvzongcheng.gitee.io/%E6%9C%BA%E5%99%A8%E8%87%AA%E5%8A%A8%E5%8C%96" title="Show list of 机器自动化">机器自动化</a>

<a href="https://lvzongcheng.gitee.io/%E6%B1%87%E7%BC%96" title="Show list of 汇编">汇编</a>

<a href="https://lvzongcheng.gitee.io/%E8%AE%B0%E4%BA%8B%E6%9C%AC" title="Show list of 记事本">记事本</a>


</nav>

<div class="container nav secondary no-print">
  
<a id="contact-link-email" class="contact_link" rel="me" aria-label="Email" href="mailto:Lyc19880405@163.com">
  <span class="fa fa-envelope-square"></span></a>



<a id="contact-link-github" class="contact_link" rel="me" aria-label="Github" href="https://github.com/enten/hugo-boilerplate">
  <span class="fa fa-github-square"></span></a>




 


















</div>


  

</header>


<article id="main-content" class="container main_content single">
  <header class="container hat">
  <h1>SQL优化
</h1>

</header>

  <div class="container content">
  <h2 id="mysql">MySql</h2>
<ol>
<li>
<p>对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。</p>
</li>
<li>
<p>应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：
select id from t where num is null
可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：</p>
</li>
</ol>
<div class="highlight"><pre style="color:#f8f8f2;background-color:#272822;-moz-tab-size:4;-o-tab-size:4;tab-size:4"><code class="language-sql" data-lang="sql"><span style="color:#66d9ef">select</span> id <span style="color:#66d9ef">from</span> t <span style="color:#66d9ef">where</span> num<span style="color:#f92672">=</span><span style="color:#ae81ff">0</span>
</code></pre></div><ol start="3">
<li>
<p>应尽量避免在 where 子句中使用!=或&lt;&gt;操作符，否则将引擎放弃使用索引而进行全表扫描。</p>
</li>
<li>
<p>应尽量避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，如：</p>
</li>
</ol>
<div class="highlight"><pre style="color:#f8f8f2;background-color:#272822;-moz-tab-size:4;-o-tab-size:4;tab-size:4"><code class="language-sql" data-lang="sql"><span style="color:#66d9ef">select</span> id <span style="color:#66d9ef">from</span> t <span style="color:#66d9ef">where</span> num<span style="color:#f92672">=</span><span style="color:#ae81ff">10</span> <span style="color:#66d9ef">or</span> num<span style="color:#f92672">=</span><span style="color:#ae81ff">20</span>
</code></pre></div><p>可以这样查询：</p>
<div class="highlight"><pre style="color:#f8f8f2;background-color:#272822;-moz-tab-size:4;-o-tab-size:4;tab-size:4"><code class="language-sql" data-lang="sql"><span style="color:#66d9ef">select</span> id <span style="color:#66d9ef">from</span> t <span style="color:#66d9ef">where</span> num<span style="color:#f92672">=</span><span style="color:#ae81ff">10</span>
<span style="color:#66d9ef">union</span> <span style="color:#66d9ef">all</span>
<span style="color:#66d9ef">select</span> id <span style="color:#66d9ef">from</span> t <span style="color:#66d9ef">where</span> num<span style="color:#f92672">=</span><span style="color:#ae81ff">20</span>
</code></pre></div><ol start="5">
<li>in 和 not in 也要慎用，否则会导致全表扫描，如：</li>
</ol>
<div class="highlight"><pre style="color:#f8f8f2;background-color:#272822;-moz-tab-size:4;-o-tab-size:4;tab-size:4"><code class="language-sql" data-lang="sql"><span style="color:#66d9ef">select</span> id <span style="color:#66d9ef">from</span> t <span style="color:#66d9ef">where</span> num <span style="color:#66d9ef">in</span>(<span style="color:#ae81ff">1</span>,<span style="color:#ae81ff">2</span>,<span style="color:#ae81ff">3</span>)
</code></pre></div><p>对于连续的数值，能用 between 就不要用 in 了：</p>
<div class="highlight"><pre style="color:#f8f8f2;background-color:#272822;-moz-tab-size:4;-o-tab-size:4;tab-size:4"><code class="language-sql" data-lang="sql"><span style="color:#66d9ef">select</span> id <span style="color:#66d9ef">from</span> t <span style="color:#66d9ef">where</span> num <span style="color:#66d9ef">between</span> <span style="color:#ae81ff">1</span> <span style="color:#66d9ef">and</span> <span style="color:#ae81ff">3</span>
</code></pre></div><ol start="6">
<li>下面的查询也将导致全表扫描：</li>
</ol>
<div class="highlight"><pre style="color:#f8f8f2;background-color:#272822;-moz-tab-size:4;-o-tab-size:4;tab-size:4"><code class="language-sql" data-lang="sql"><span style="color:#66d9ef">select</span> id <span style="color:#66d9ef">from</span> t <span style="color:#66d9ef">where</span> name <span style="color:#66d9ef">like</span> <span style="color:#e6db74">&#39;</span><span style="color:#e6db74">%abc%</span><span style="color:#e6db74">&#39;</span>
</code></pre></div><p>若要提高效率，可以考虑全文检索。</p>
<ol start="7">
<li>如果在 where 子句中使用参数，也会导致全表扫描。因为SQL只有在运行时才会解析局部变量，但优化程序不能将访问计划的选择推迟到运行时；它必须在编译时进行选择。然而，如果在编译时建立访问计划，变量的值还是未知的，因而无法作为索引选择的输入项。如下面语句将进行全表扫描：</li>
</ol>
<div class="highlight"><pre style="color:#f8f8f2;background-color:#272822;-moz-tab-size:4;-o-tab-size:4;tab-size:4"><code class="language-sql" data-lang="sql"><span style="color:#66d9ef">select</span> id <span style="color:#66d9ef">from</span> t <span style="color:#66d9ef">where</span> num<span style="color:#f92672">=</span><span style="color:#f92672">@</span>num
</code></pre></div><p>可以改为强制查询使用索引：</p>
<div class="highlight"><pre style="color:#f8f8f2;background-color:#272822;-moz-tab-size:4;-o-tab-size:4;tab-size:4"><code class="language-sql" data-lang="sql"><span style="color:#66d9ef">select</span> id <span style="color:#66d9ef">from</span> t <span style="color:#66d9ef">with</span>(<span style="color:#66d9ef">index</span>(<span style="color:#960050;background-color:#1e0010">索</span><span style="color:#960050;background-color:#1e0010">引</span><span style="color:#960050;background-color:#1e0010">名</span>)) <span style="color:#66d9ef">where</span> num<span style="color:#f92672">=</span><span style="color:#f92672">@</span>num
</code></pre></div><ol start="8">
<li>应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。如：</li>
</ol>
<div class="highlight"><pre style="color:#f8f8f2;background-color:#272822;-moz-tab-size:4;-o-tab-size:4;tab-size:4"><code class="language-sql" data-lang="sql"><span style="color:#66d9ef">select</span> id <span style="color:#66d9ef">from</span> t <span style="color:#66d9ef">where</span> num<span style="color:#f92672">/</span><span style="color:#ae81ff">2</span><span style="color:#f92672">=</span><span style="color:#ae81ff">100</span>
</code></pre></div><p>应改为:</p>
<div class="highlight"><pre style="color:#f8f8f2;background-color:#272822;-moz-tab-size:4;-o-tab-size:4;tab-size:4"><code class="language-sql" data-lang="sql"><span style="color:#66d9ef">select</span> id <span style="color:#66d9ef">from</span> t <span style="color:#66d9ef">where</span> num<span style="color:#f92672">=</span><span style="color:#ae81ff">100</span><span style="color:#f92672">*</span><span style="color:#ae81ff">2</span>
</code></pre></div><ol start="9">
<li>应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。如：</li>
</ol>
<div class="highlight"><pre style="color:#f8f8f2;background-color:#272822;-moz-tab-size:4;-o-tab-size:4;tab-size:4"><code class="language-sql" data-lang="sql"><span style="color:#66d9ef">select</span> id <span style="color:#66d9ef">from</span> t <span style="color:#66d9ef">where</span> <span style="color:#66d9ef">substring</span>(name,<span style="color:#ae81ff">1</span>,<span style="color:#ae81ff">3</span>)<span style="color:#f92672">=</span><span style="color:#e6db74">&#39;</span><span style="color:#e6db74">abc</span><span style="color:#e6db74">&#39;</span><span style="color:#75715e">--name以abc开头的id
</span><span style="color:#75715e"></span><span style="color:#66d9ef">select</span> id <span style="color:#66d9ef">from</span> t <span style="color:#66d9ef">where</span> datediff(<span style="color:#66d9ef">day</span>,createdate,<span style="color:#e6db74">&#39;</span><span style="color:#e6db74">2005-11-30</span><span style="color:#e6db74">&#39;</span>)<span style="color:#f92672">=</span><span style="color:#ae81ff">0</span><span style="color:#75715e">--‘2005-11-30’生成的id
</span></code></pre></div><p>应改为:</p>
<div class="highlight"><pre style="color:#f8f8f2;background-color:#272822;-moz-tab-size:4;-o-tab-size:4;tab-size:4"><code class="language-sql" data-lang="sql"><span style="color:#66d9ef">select</span> id <span style="color:#66d9ef">from</span> t <span style="color:#66d9ef">where</span> name <span style="color:#66d9ef">like</span> <span style="color:#e6db74">&#39;</span><span style="color:#e6db74">abc%</span><span style="color:#e6db74">&#39;</span>
<span style="color:#66d9ef">select</span> id <span style="color:#66d9ef">from</span> t <span style="color:#66d9ef">where</span> createdate<span style="color:#f92672">&gt;</span><span style="color:#f92672">=</span><span style="color:#e6db74">&#39;</span><span style="color:#e6db74">2005-11-30</span><span style="color:#e6db74">&#39;</span> <span style="color:#66d9ef">and</span> createdate<span style="color:#f92672">&lt;</span><span style="color:#e6db74">&#39;</span><span style="color:#e6db74">2005-12-1</span><span style="color:#e6db74">&#39;</span>
</code></pre></div><ol start="10">
<li>
<p>不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。</p>
</li>
<li>
<p>在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相一致。</p>
</li>
<li>
<p>不要写一些没有意义的查询，如需要生成一个空表结构：</p>
</li>
</ol>
<div class="highlight"><pre style="color:#f8f8f2;background-color:#272822;-moz-tab-size:4;-o-tab-size:4;tab-size:4"><code class="language-sql" data-lang="sql"><span style="color:#66d9ef">select</span> col1,col2 <span style="color:#66d9ef">into</span> <span style="color:#f92672">#</span>t <span style="color:#66d9ef">from</span> t <span style="color:#66d9ef">where</span> <span style="color:#ae81ff">1</span><span style="color:#f92672">=</span><span style="color:#ae81ff">0</span>
</code></pre></div><p>这类代码不会返回任何结果集，但是会消耗系统资源的，应改成这样：</p>
<div class="highlight"><pre style="color:#f8f8f2;background-color:#272822;-moz-tab-size:4;-o-tab-size:4;tab-size:4"><code class="language-sql" data-lang="sql"><span style="color:#66d9ef">create</span> <span style="color:#66d9ef">table</span> <span style="color:#f92672">#</span>t(...)
</code></pre></div><ol start="13">
<li>很多时候用 exists 代替 in 是一个好的选择：</li>
</ol>
<div class="highlight"><pre style="color:#f8f8f2;background-color:#272822;-moz-tab-size:4;-o-tab-size:4;tab-size:4"><code class="language-sql" data-lang="sql">       <span style="color:#66d9ef">select</span> num <span style="color:#66d9ef">from</span> a <span style="color:#66d9ef">where</span> num <span style="color:#66d9ef">in</span>(<span style="color:#66d9ef">select</span> num <span style="color:#66d9ef">from</span> b)
</code></pre></div><p>用下面的语句替换：</p>
<div class="highlight"><pre style="color:#f8f8f2;background-color:#272822;-moz-tab-size:4;-o-tab-size:4;tab-size:4"><code class="language-sql" data-lang="sql"><span style="color:#66d9ef">select</span> num <span style="color:#66d9ef">from</span> a <span style="color:#66d9ef">where</span> <span style="color:#66d9ef">exists</span>(<span style="color:#66d9ef">select</span> <span style="color:#ae81ff">1</span> <span style="color:#66d9ef">from</span> b <span style="color:#66d9ef">where</span> num<span style="color:#f92672">=</span>a.num)
</code></pre></div><ol start="14">
<li>
<p>并不是所有索引对查询都有效，SQL是根据表中数据来进行查询优化的，当索引列有大量数据重复时，SQL查询可能不会去利用索引，如一表中有字段sex，male、female几乎各一半，那么即使在sex上建了索引也对查询效率起不了作用。</p>
</li>
<li>
<p>索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率，因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。一个表的索引数最好不要超过6个，若太多则应考虑一些不常使用到的列上建的索引是否有必要。</p>
</li>
<li>
<p>应尽可能的避免更新 clustered 聚合索引数据列，因为 clustered 聚合索引数据列的顺序就是表记录的物理存储顺序，一旦该列值改变将导致整个表记录的顺序的调整，会耗费相当大的资源。若应用系统需要频繁更新 clustered 聚合索引数据列，那么需要考虑是否应将该索引建为 clustered 聚合索引。</p>
</li>
<li>
<p>尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。</p>
</li>
<li>
<p>尽可能的使用 varchar/nvarchar 代替 char/nchar ，因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。</p>
</li>
<li>
<p>任何地方都不要使用 select * from t ，用具体的字段列表代替“*”，不要返回用不到的任何字段。</p>
</li>
<li>
<p>尽量使用表变量来代替临时表。如果表变量包含大量数据，请注意索引非常有限（只有主键索引）。<br>
<a href="https://www.cnblogs.com/freshman0216/archive/2010/11/14/1868672.html">表变量和临时表</a></p>
</li>
<li>
<p>避免频繁创建和删除临时表，以减少系统表资源的消耗。</p>
</li>
<li>
<p>临时表并不是不可使用，适当地使用它们可以使某些例程更有效，例如，当需要重复引用大型表或常用表中的某个数据集时。但是，对于一次性事件，最好使用导出表。</p>
</li>
<li>
<p>在新建临时表时，如果一次性插入数据量很大，那么可以使用 select into 代替 create table，避免造成大量 log ，以提高速度；如果数据量不大，为了缓和系统表的资源，应先create table，然后insert。</p>
</li>
<li>
<p>如果使用到了临时表，在存储过程的最后务必将所有的临时表显式删除，先 truncate table ，然后 drop table ，这样可以避免系统表的较长时间锁定。</p>
</li>
<li>
<p>尽量避免使用游标，因为游标的效率较差，如果游标操作的数据超过1万行，那么就应该考虑改写。</p>
</li>
<li>
<p>使用基于游标的方法或临时表方法之前，应先寻找基于集的解决方案来解决问题，基于集的方法通常更有效。</p>
</li>
<li>
<p>与临时表一样，游标并不是不可使用。对小型数据集使用 FAST_FORWARD 游标通常要优于其他逐行处理方法，尤其是在必须引用几个表才能获得所需的数据时。在结果集中包括“合计”的例程通常要比使用游标执行的速度快。如果开发时 间允许，基于游标的方法和基于集的方法都可以尝试一下，看哪一种方法的效果更好。</p>
</li>
<li>
<p>在所有的存储过程和触发器的开始处设置 SET NOCOUNT ON ，在结束时设置 SET NOCOUNT OFF 。无需在执行存储过程和触发器的每个语句后向客户端发送 DONE_IN_PROC 消息。</p>
</li>
<li>
<p>尽量避免大事务操作，提高系统并发能力。</p>
</li>
<li>
<p>尽量避免向客户端返回大数据量，若数据量过大，应该考虑相应需求是否合理。</p>
</li>
</ol>
<p><a href="https://www.cnblogs.com/xiaoboluo768/p/5400990.html">mysql explain执行计划详解</a></p>
<h3 id="explain-">EXPLAIN 执行计划</h3>
<p>(1. extra 列包含“Using temporarg” 说明这个查询包含了隐式查询
访问类型type,结果值从好到坏依次是：<br>
system &gt; const &gt; eq_ref &gt; ref &gt; fulltext &gt; ref_or_null &gt; index_merge &gt; unique_subquery &gt; index_subquery &gt; range &gt; index &gt; ALL
一般，好的sql查询至少达到range级别，最好能达到ref</p>
<h3 id="heading">错误和解决方法</h3>
<h5 id="mysql1">在MySQL中创建函数时出现这种错误的解决方法：</h5>
<p>错误：This function has none of DETERMINISTIC,NO SQL,or READS SQL DATA in its declaration and binary logging is enabled 
(you <em>might</em> want to use the less safe log_bin_trust_function_creators variable)</p>
<p>解决方法：</p>
<pre><code> set global log_bin_trust_function_creators=TRUE;
</code></pre><h2 id="oracle">Oracle</h2>

</div>


  
</article>
      <footer id="main-footer" class="container main_footer">
  

  <div class="container nav foot no-print">
  
<a href="https://lvzongcheng.gitee.io/license">license</a>


  <a class="toplink" href="#">back to top</a>

</div>

  <div class="container credits">
  
<div class="container footline">
  
  code with <!-- raw HTML omitted --><!-- raw HTML omitted -->


</div>


  
<div class="container copyright">
  
  (c) 2015 yourname.


</div>


</div>

</footer>

    </main>
    
<script type="text/javascript">
  (function() {
    
    
    if (window.location.hostname == "localhost")
      return;
    var dsq = document.createElement('script'); dsq.async = true; dsq.type = 'text/javascript';
    dsq.src = '//your_disqus_shortname.disqus.com/count.js';
    (document.getElementsByTagName('head')[0] || document.getElementsByTagName('body')[0]).appendChild(dsq);
  })();
</script>



<script src="/js/highlight.pack.js"></script>
<script>hljs.initHighlightingOnLoad();</script>



    
  </body>
</html>

